import traceback
import aiomysql
import asyncio
import time
from typing import Union

from configs import config

__delta__ = time.perf_counter()


async def create_pool():
    return aiomysql.create_pool(host=config.MYSQL_HOST, port=config.MYSQL_PORT, user=config.MYSQL_USER,
                                password=config.MYSQL_PASSWD,
                                db=config.MYSQL_DB,
                                loop=asyncio.get_event_loop(), autocommit=False,
                                maxsize=config.MYSQL_CONNECTION_MAXSIZE)


async def execute(sql: str, args: Union[tuple, list] = None) -> (int, list):
    conn: aiomysql.Connection
    cursor: aiomysql.DictCursor
    rows: int
    res: list

    # config.logger.debug('waiting conn...')
    async with config.pool.acquire() as conn:
        # config.logger.debug('waiting curso...')
        async with conn.cursor(aiomysql.DictCursor) as cursor:
            try:
                # config.logger.debug('waiting execute...')
                rows = await cursor.execute(sql, args)
                # config.logger.debug('waiting fetch...')
                res = await cursor.fetchall()
                return rows, res
            except Exception as e:
                # config.logger.debug('waiting ping...')
                config.logger.error(e)
                await conn.ping()
                # config.logger.debug('waiting execute again...')
                rows = await cursor.execute(sql, args)
                # config.logger.debug('waiting fetch again...')
                res = await cursor.fetchall()
                return rows, res


async def execute_tuple(sql: str, args: Union[tuple, list] = None) -> (int, list):
    conn: aiomysql.Connection
    cursor: aiomysql.DictCursor
    rows: int
    res: list

    # config.logger.debug('waiting conn...')
    async with config.pool.acquire() as conn:
        # config.logger.debug('waiting curso...')
        async with conn.cursor() as cursor:
            try:
                # config.logger.debug('waiting execute...')
                rows = await cursor.execute(sql, args)
                # config.logger.debug('waiting fetch...')
                res = await cursor.fetchall()
                return rows, res
            except Exception as e:
                # config.logger.debug('waiting ping...')
                await conn.ping()
                # config.logger.debug('waiting execute again...')
                rows = await cursor.execute(sql, args)
                # config.logger.debug('waiting fetch again...')
                res = await cursor.fetchall()
                return rows, res


async def execute_sort(sql: str, args: Union[tuple, list] = None) -> list:
    conn: aiomysql.Connection
    cursor: aiomysql.DictCursor
    rows: int
    res: list

    config.logger.debug('waiting conn...')
    async with config.pool.acquire() as conn:
        config.logger.debug('waiting curso...')
        async with conn.cursor(aiomysql.DictCursor) as cursor:
            spus = []
            try:
                for spu_id in args:
                    await cursor.execute(sql, spu_id)
                    spu = await cursor.fetchall()
                    spus.append({
                        'id': spu[0].get('id'),
                        'title': spu[0].get('title'),
                        'price': float(spu[0].get('price')),
                        'img': 'http://81.71.137.167:8000/content/' + spu[0].get('img')
                    })
                return spus
            except Exception as e:
                await conn.ping()
                spus = []
                for spu_id in args:
                    await cursor.execute(sql, spu_id)
                    spu = await cursor.fetchall()
                    spus.append({
                        'id': spu[0].get('id'),
                        'title': spu[0].get('title'),
                        'price': float(spu[0].get('price')),
                        'img': 'http://81.71.137.167:8000/content/' + spu[0].get('img')
                    })
                return spus


async def execute_with_commit(sql: str, args: Union[tuple, list] = None) -> int:
    conn: aiomysql.Connection
    cursor: aiomysql.Cursor
    rows: int
    print(sql)

    async with config.pool.acquire() as conn:
        async with conn.cursor(aiomysql.Cursor) as cursor:
            try:
                rows = await cursor.execute(sql, args)
                await conn.commit()
                # config.logger.debug(f"rows effect: {cursor._rowcount}")
                return rows
            except Exception as e:

                # config.logger.debug('execute..again.................')
                # print(e.__str__())

                config.logger.debug('execute..again.................')
                config.logger.debug(e.__str__())

                await conn.ping()
                await cursor.execute(sql, args)
                await conn.commit()
                return conn.affected_rows()


async def execute_many(sql, data):
    conn: aiomysql.Connection
    cursor: aiomysql.Cursor
    rows: int

    conn = await config.pool.acquire()
    cursor = await conn.cursor(aiomysql.Cursor)
    await cursor.executemany(sql, data)
    await conn.commit()
    await cursor.close()
    config.pool.release(conn)
    return

# async def transaction(sqls:list,args:[[],[]]):
#     assert isinstance(sqls, list)
#     conn: aiomysql.Connection
#     cursor: aiomysql.Cursor
#     rows: int = 0
#
#     conn = await config.pool.acquire()
#     cursor = await conn.cursor(aiomysql.Cursor)
#     await conn.begin()
#     try:
#         for index,sql in enumerate(sqls):
#             await cursor.execute(sql,args[index])
#         rows = cursor.rowcount
#     except Exception as e:
#         print("transaction err:" + e.__str__())
#         await conn.rollback()
#     else:
#         await conn.commit()
#     finally:
#         await config.pool.release(conn)
#         await cursor.close()
#         return rows

